﻿Imports ESRI.ArcGIS.Carto
Imports ESRI.ArcGIS.Geodatabase
Imports ESRI.ArcGIS.DataSourcesFile
Imports System.Data.SqlClient

Module SiteConfigUtils

    Public Function getSiteConfigPath(ByVal codeSite As String) As String
        Dim tempDir As String = getWorkSpaceSitesPath() & System.IO.Path.DirectorySeparatorChar & codeSite & System.IO.Path.DirectorySeparatorChar & "Configuration"
        createDirectory(tempDir)
        Return tempDir
    End Function

    Public Function WorkSpaceProjectName() As String
        Return Application.StartupPath() & System.IO.Path.DirectorySeparatorChar & "Images/"
    End Function
    Public Function getSiteImagesPath(ByVal codeSite As String) As String
        Dim tempDir As String = getWorkSpaceSitesPath() & System.IO.Path.DirectorySeparatorChar & codeSite & System.IO.Path.DirectorySeparatorChar & "Multimedia"
        createDirectory(tempDir)
        Return tempDir

    End Function

    Public Function getSiteRootPath(ByVal codeSite As String) As String
        Dim tempDir As String = getWorkSpaceSitesPath() & System.IO.Path.DirectorySeparatorChar & codeSite
        createDirectory(tempDir)
        Return tempDir

    End Function

    Public Function getSiteXmlFilePath(ByVal codeSite As String) As String
        Return getSiteConfigPath(codeSite) & System.IO.Path.DirectorySeparatorChar & codeSite & ".xml"
    End Function

    Public Function getSiteConfigFilePath(ByVal codeSite As String) As String
        Return getSiteConfigPath(codeSite) & System.IO.Path.DirectorySeparatorChar & codeSite & ".silm"
    End Function

    Public Function getWorkSpacePath() As String
        Dim tempDir As String = Settings.Default.WorkSpacePath
        createDirectory(tempDir)
        Return tempDir
    End Function

    Public Function getWorkSpaceSitesPath() As String
        Dim tempDir As String = getWorkSpacePath() & System.IO.Path.DirectorySeparatorChar & "Sites"
        createDirectory(tempDir)
        Return tempDir
    End Function
    Public Function getWorkSpaceConfigLayerPath() As String
        Dim tempDir As String = getWorkSpacePath() & System.IO.Path.DirectorySeparatorChar & "Couches"
        createDirectory(tempDir)
        Return tempDir

    End Function
    Public Function getSiteMapDocumentFilePath(ByVal codeSite As String) As String
        Return getSiteConfigPath(codeSite) & System.IO.Path.DirectorySeparatorChar & codeSite & ".mxd"
    End Function

    Public Function getSiteLayersPath(ByVal codeSite As String) As String
        Dim tempDir As String = getWorkSpaceSitesPath() & System.IO.Path.DirectorySeparatorChar & codeSite & System.IO.Path.DirectorySeparatorChar & "Layers"
        createDirectory(tempDir)
        Return tempDir

    End Function

    Public Function getSiteDefaultLayerPath() As String
        Return Application.StartupPath() & System.IO.Path.DirectorySeparatorChar & "Ressources\PreparedLayers"
    End Function

    Public Function getSiteDatabasePath(ByVal codeSite As String) As String
        Dim tempDir As String = getWorkSpaceSitesPath() & System.IO.Path.DirectorySeparatorChar & codeSite & System.IO.Path.DirectorySeparatorChar & "Database"
        createDirectory(tempDir)
        Return tempDir


    End Function

    Public Function getSiteSqlPath(ByVal codeSite As String) As String
        Return Application.StartupPath() & System.IO.Path.DirectorySeparatorChar & "Ressources\sql\1.0\SITE-1.0-tables.sql"
    End Function

    Public Function getSiteDelimitationsPath() As String
        Return Application.StartupPath() & System.IO.Path.DirectorySeparatorChar & "Ressources\delimitationLayers"
    End Function

    Public Sub createDirectory(ByVal rep As String)
        Try
            If rep Is Nothing OrElse rep.Trim().Length = 0 Then
                Return
            End If
            If IO.Directory.Exists(rep) Then
                Return
            End If

            IO.Directory.CreateDirectory(rep)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical)
        End Try
    End Sub

    Public Sub addLayerToMap(ByVal layerName As String, ByVal pMap As IMap, ByVal workspacePath As String)
        Dim workspaceFactory As IWorkspaceFactory = New ShapefileWorkspaceFactory()
        Dim featureWorkspace As IFeatureWorkspace = TryCast(workspaceFactory.OpenFromFile(workspacePath, 0), IFeatureWorkspace)
        ' Create a new FeatureLayer and assign a shapefile to it
        Dim featureLayer As IFeatureLayer = New FeatureLayerClass
        featureLayer.FeatureClass = featureWorkspace.OpenFeatureClass(layerName)
        Dim layer As ILayer = featureLayer
        layer.Name = featureLayer.FeatureClass.AliasName
        ' Add the Layer to the focus map
        pMap.AddLayer(layer)
    End Sub

    Public Function NameWithoutExtention(ByVal FileFullName As String) As String
        Dim intPos As Integer
        intPos = FileFullName.LastIndexOfAny(".")
        Return FileFullName.Substring(0, intPos)
    End Function

    Public Function NameOnlyFromFullPath(ByVal FileFullPath As String) As String
        'EXAMPLE: input ="C:\winnt\system32\kernel.dll, 
        'output = kernel.dll
        Dim intPos As Integer
        intPos = FileFullPath.LastIndexOfAny("\")
        intPos += 1
        Return FileFullPath.Substring(intPos, (Len(FileFullPath) - intPos))
    End Function

    Public Function FolderFromFileName(ByVal FileFullPath As String) As String
        'EXAMPLE: input ="C:\winnt\system32\kernel.dll, 
        'output = C:\winnt\system32\
        Dim intPos As Integer
        intPos = FileFullPath.LastIndexOfAny("\")
        intPos += 1
        Return FileFullPath.Substring(0, intPos)
    End Function
    Public Function isColExistInTable(ByVal colName As String, ByVal tableName As String, ByVal dbConnexion As String) As Boolean
        Dim con As SqlConnection = New SqlConnection(dbConnexion)
        Try
            con.Open()
            Dim query As String = "select c.name col  from sysobjects t, syscolumns c where t.id= c.id and  t.name = '" & tableName & "'"
            Dim myCmd As SqlDataAdapter = New SqlDataAdapter(query, con)
            Dim myData As New DataSet()
            myCmd.Fill(myData)
            For Each table As DataTable In myData.Tables
                For Each row As DataRow In table.Rows
                    For Each col As DataColumn In table.Columns
                        If colName = colName Then
                            Return True
                        End If
                    Next
                Next
            Next
            con.Close()
        Catch ex As Exception

        End Try
        Return False
    End Function
    Public Function GetUserTables(ByVal dbConnexion As String) As DataTableCollection
        Dim con As SqlConnection = New SqlConnection(dbConnexion)
        Try
            con.Open()
            Dim query As String = "SELECT name FROM sysobjects WHERE xtype='U'"
            Dim myCmd As SqlDataAdapter = New SqlDataAdapter(query, con)
            Dim myData As New DataSet()
            myCmd.Fill(myData)
            Return myData.Tables
            'cboxTables.Items.Clear()
            'cboxTables.Items.Add("")
            'For Each table As DataTable In myData.Tables
            '    For Each row As DataRow In table.Rows
            '        For Each col As DataColumn In table.Columns
            '            cboxTables.Items.Add(row(col).ToString())
            '        Next
            '    Next
            'Next
            'con.Close()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Sub AddFieldToFeatureClass(ByVal featureClass As IFeatureClass, ByVal field As IField)

        Dim schemaLock As ISchemaLock = CType(featureClass, ISchemaLock)

        Try
            ' A try block is necessary, as an exclusive lock may not be available.
            schemaLock.ChangeSchemaLock(esriSchemaLock.esriExclusiveSchemaLock)

            ' Add the field.
            featureClass.AddField(field)
        Catch exc As Exception
            ' Handle this in a way appropriate to your application.
            Console.WriteLine(exc.Message)
        Finally
            ' Set the lock to shared, whether or not an error occurred.
            schemaLock.ChangeSchemaLock(esriSchemaLock.esriSharedSchemaLock)
        End Try

    End Sub
    Public Function createField(ByVal fieldName As String, ByVal fieldAlias As String, ByVal fieldType As esriFieldType) As Field
        Dim field As IField = New Field()
        Dim fieldEdit As IFieldEdit = CType(field, IFieldEdit)
        fieldEdit.AliasName_2 = fieldAlias
        fieldEdit.Editable_2 = True
        fieldEdit.IsNullable_2 = False
        fieldEdit.Name_2 = fieldName
        fieldEdit.Type_2 = fieldType
        Return field
    End Function

    Public Function getSiteDatabaseString(ByVal codeSite As String) As String
        Dim ConnectionString As String = Settings.Default.ConnectionStr '"Data Source=localhost\sqlexpress;Initial Catalog=silm_11;User Id=sa;Password=sa"
        Dim tokens As String() = ConnectionString.Split(";")
        tokens(1) = "Initial Catalog=" & codeSite
        ConnectionString = tokens(0) & ";" & tokens(1) & ";" & tokens(2) & ";" & tokens(3)
        Return ConnectionString
    End Function
End Module
